import json
import datetime
import requests
import psycopg2
import time

with open("./config.json", "r", encoding='utf-8') as config:
    config_dict = json.load(config)


def is_table(tablename):
    cursor = conn.cursor()
    cursor.execute("select count(*) from pg_class where relname = '%s'" % tablename)
    rows = cursor.fetchall()
    cursor.close()
    return True if rows[0][0] else False


def execute_sql(sql):
    # 建立游标，用来执行数据库操作
    cursor = conn.cursor()
    # 执行SQL命令
    cursor.execute(sql)
    # 提交SQL命令
    conn.commit()
    # 关闭游标
    cursor.close()


def search_sql(sql):
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    if len(result) > 0:
        return True
    else:
        return False


def write_log(string):
    log = open('log.txt', 'a')
    log.write(string+ '\n')
    log.close()


def get_requests(url, data):
    r = requests.get(url=url, params=data)
    return r


if __name__ == '__main__':
    print("服务器正在运行中.......")
    try:
        while True:
            # 连接数据库
            write_log(str(datetime.datetime.now()))
            # print(datetime.datetime.now())
            nowTime = datetime.datetime.now()
            conn = psycopg2.connect(database=config_dict["db"], user=config_dict["username"],
                                    password=config_dict["password"], host=config_dict["host"],
                                    port=config_dict["port"])
            res = is_table(tablename=config_dict["table"])
            if not res:
                # 创建数据库
                execute_sql(
                    "CREATE TABLE %s(id serial not null primary key, gateway text, device text, name text,  status text, timestamp int, val text,time date)" %
                    config_dict["table"])
            for gateway in config_dict["gateway"]:
                for device in gateway['device']:
                    # 请求网关http接口
                    res = get_requests(gateway["address"] + 'API/V2/real', {'deviceCode': device})
                    if res.status_code == 200:
                        write_log(str(res.json()))
                        # print(res.json())
                        data = res.json()
                        if data['deviceCode'] == device:
                            for v in data['val']:
                                sql = "INSERT INTO {}(gateway, device, name, status, timestamp, val, time) VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}')".format(config_dict["table"], gateway["address"],
                                                                          device, v['id'].split('.')[1], v['status'],
                                                                          v['timestamp'], v['val'], nowTime)
                                # 判断数据库是否包含该数据
                                # sql = "select * from {} where gateway='{}' and device='{}' and name='{}'".format(
                                #     config_dict["table"], gateway["address"], device, v['id'].split('.')[1])
                                # if search_sql(sql):
                                #     sql = "UPDATE {} SET status='{}',timestamp='{}',val='{}' where gateway='{}' and " \
                                #           "device='{}' and name='{}'".format(config_dict["table"], v['status'],
                                #                                              v['timestamp'], v['val'],
                                #                                              gateway["address"], device,
                                #                                              v['id'].split('.')[1])
                                # else:
                                #     sql = "INSERT INTO {}(gateway, device, name, status, timestamp, val) VALUES ('{}', '{}', '{}', '{}', '{}', '{}')".format(config_dict["table"], gateway["address"],
                                #                                           device, v['id'].split('.')[1], v['status'],
                                #                                           v['timestamp'], v['val'])
                                write_log(sql)
                                execute_sql(sql)
                    else:
                        print("Service request failed")
            # 关闭数据库
            conn.close()
            # 赞叹1分钟
            time.sleep(60)
    except Exception as e:
        write_log(str(e))
        print(e)
